<?php

namespace XWAM\Addon\PXCallSystem\Controller;

/**
 * 统计分析控制器
 *
 * @author Alen
 * @since 1.0 2014-6-19 by sutroon
 */
class StaticsController extends AppbaseController {

    function day_bill_search() {
        $search = I();
        if (!$this->isSuperAdmin) {
            $where['siteID'] = $this->loginData['siteID'];
        }
        if (!$search['beginDate']) {
            $search['beginDate'] = date('Y-m-01'); // 默认当月1号
        }
        $where["date_format(createdTime,'%Y-%m-%d')"][] = array('EGT', $search['beginDate']);
        if ($search['endDate']) {
            $where["date_format(createdTime,'%Y-%m-%d')"][] = array('ELT', $search['endDate']);
        }
        if ($search['siteID']) {
            $where['siteID'] = $search['siteID'];
        }
        return array('where' => $where, 'search' => $search);
    }

    // 日结账单
    public function day_bill() {
        $search_data = $this->day_bill_search();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    function daybill_export() {
        $search_data = $this->day_bill_search();
        $list = AddonD('DayBill')->scope('export')->where($search_data['where'])->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    function get_daybill_list_json() {
        $search_data = $this->day_bill_search();
        $this->ajaxReturn(AddonD('DayBill')->get_daybill_list($search_data['where']));
    }

    function get_cdr_table() {
        $table = sofn_get_sharding_field('DB_CDR') . 'cdr';
        if (I('date') != date('Y-m-d')) {
            if (I('siteID')) {
                $table = sofn_get_sharding_field('DB_CDR_HISTORY') . 'cdr_' . I('siteID');
            }
        }
        return $table;
    }

    // 通话详单
    public function call_history() {
        // 每天10点前每30分钟一次分表 2014-11-20 by sutroon
        $flag = S('cdr-split-flag');
        if (date('H') < '12' && !$flag) {
            $this->split_cdr_table();
            S('cdr-split-flag', '1', array('expire' => 1800));
        }
        $this->display();
    }

    function get_cdr_search_data() {
        $search = I('post.');
        $strings = false;
        if ($search['callerNo']) {
            $where['callerNo'] = array('like', '%' . $search['callerNo'] . '%');
        }
        if ($search['dst']) {
            $where['dst'] = array('like', '%' . $search['dst'] . '%');
        } else {
            $strings[] = "LENGTH(dst) > 2 and dst not like '555%'";
        }
        if ($search['date']) {
            $strings[] = "date_format(start,'%Y-%m-%d') = '" . $search['date'] . "'";
        }
        if ($search['extension']) {
            $where['extension'] = $search['extension'];
        }
        if ($search['direction']) {
            $where['direction'] = $search['direction'];
        } else {
            $where['direction'] = array('GT', 0);
        }
        if ($search['billsec']) {
            $where['billsec'][] = array('GT', $search['billsec']);
        }
        if ($search['billsecl']) {
            $where['billsec'][] = array('LT', $search['billsecl']);
        }
        if ($search['strategy']) {
            $where['taskStrategyID'] = $search['strategy'];
        }
        if ($search['task']) {
            $where['taskID'] = $search['task'];
        }
        if ($search['dtmf']) {
            if ('*' == $where['dtmf']) {
                $where['dtmf'] = array('NEQ', '');
            } else {
                $where['dtmf'] = array('like', '%' . $search['dtmf'] . '%');
            }
        }
        if ($search['ecdtmf']) {
            if ('*' == $where['ecdtmf']) {
                $where['ecdtmf'] = array('NEQ', '');
            } else {
                $where['ecdtmf'] = array('like', '%' . $search['ecdtmf'] . '%');
            }
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            if ('TTF' != C('USER.USER_KEY')) {
                $search['siteID'] = $this->loginData['siteID'];
                $where['siteID'] = $search['siteID'];
            }
        }
        if ($strings) {
            $where['_string'] = implode(' and ', $strings);
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_cdr_list_json() {
        $search_data = $this->get_cdr_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->get_cdr_list($search_data['where'], $table);
        $this->ajaxReturn($list);
    }

    public function cdr_export() {
        $search_data = $this->get_cdr_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->scope('export')->where($search_data['where'])->cache(false)->table($table)->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 通话详单
    public function call_history_lite() {
// 每天10点前每30分钟一次分表 2014-11-20 by sutroon
        $flag = S('cdr-split-flag');
        if (date('H') < '12' && !$flag) {
            $this->split_cdr_table();
            S('cdr-split-flag', '1', array('expire' => 1800));
        }
        $this->assign('queue', I('queue'));
        $this->display();
    }

    public function get_cdrlite_search_data() {
        $search = I();
        $sql = '';
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $sql = 'and c.siteID=' . $search['siteID'];
            }
        } else {
            $sql = 'and c.siteID=' . $this->loginData['siteID'];
        }
        if ($search['queue']) {
            $sql = " and c.dst='" . $search['queue'] . "'";
        }
        return array('where' => $sql);
    }

    public function get_cdrlite_list_json() {
        $search_data = $this->get_cdrlite_search_data();
        $sql = $search_data['where'];
        $orderby = I('sort', 'id desc') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $table = sofn_get_sharding_field('DB_CDR') . 'cdr';
        $total_result = M()->query("select count(c.id) as n from $table c INNER JOIN tcti_acd_queue q on c.dst=q.extension where c.extension <> '' $sql");
        $total = $total_result[0]['n'];
        $list = M()->query("select c.* from $table c INNER JOIN tcti_acd_queue q on c.dst=q.extension where c.extension <> '' $sql order by c.$orderby limit $page");
        $out = array();
        foreach ($list as $row) {
            $row['directionText'] = get_channelDirection_text($row['direction']);
            $row['billsecText'] = sofn_seconds_format($row['billsec']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $this->ajaxReturn(array('total' => $total, 'rows' => $out));
    }

    public function cdrlite_export() {
        $search_data = $this->get_cdrlite_search_data();
        $sql = $search_data['where'];
        $table = sofn_get_sharding_field('DB_CDR') . 'cdr';
        $list = M()->query("select c.callerNo as '主叫号码', c.dst as '被叫号码', c.extension as '工号', c.direction as '方向', c.xcallAnswer as '开始时间', c.end as '结束时间', c.billsec as '通话时长' from $table c INNER JOIN tcti_acd_queue q on c.dst=q.extension where c.extension <> '' $sql order by c.id desc");
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    /**
     * cdr第1次手动分表
     * @since 2014-11
     */
    public function batcdr() {
        $siteIDs = M('tuser_member')->where("userType='ADMIN'")->getField('siteID', true);

        if (!$siteIDs) {
            return 'none';
        }
        $table = sofn_get_sharding_field('DB_CDR_HISTORY') . 'cdr_';
        foreach ($siteIDs as $siteID) {
            $msg = AddonD('DbTable')->create_table('CDR', $table . $siteID);
        }
        if (!$msg['status']) {
            die('Err:' . $msg['info']);
        }
    }

    /**
     * 分表,已弃用[2015-4-28 迁移到XCallConsole程序中]
     * @since 1.0 2014-11-20 by sutroon
     */
    public function split_cdr_table() {
        $this->batcdr();
        $table = sofn_get_sharding_field('DB_CDR') . 'cdr';
        $M = M($table);
        $splitwhere = "date_format(start,'%Y-%m-%d') < '" . date('Y-m-d') . "' and siteID is not null";
        $siteIDs = $M->where($splitwhere)->distinct(true)->getField('siteID', true);
        if ($siteIDs) {
            $cdr_history = sofn_get_sharding_field('DB_CDR_HISTORY') . 'cdr_';
            foreach ($siteIDs as $siteID) {
                $table = $cdr_history . $siteID;
                $sqls[] = "insert into $table (`id`, `calldate`, `clid`, `src`, `dst`, `dcontext`, `channel`, `dstchannel`, `lastapp`, `lastdata`, `start`, `xcallAnswer`, `answer`, `end`, `duration`, `billsec`, `disposition`, `amaflags`, `accountcode`, `uniqueid`, `userfield`, `did`, `recordingfile`, `cnum`, `cnam`, `outbound_cnum`, `outbound_cnam`, `dst_cnam`, `taskID`, `taskStrategyID`, `callerNo`, `calledNo`, `extension`, `hungupTime`, `seatdtmf`, `dtmf`, `ecdtmf`, `amount`, `direction`, `siteID`) select `id`, `calldate`, `clid`, `src`, `dst`, `dcontext`, `channel`, `dstchannel`, `lastapp`, `lastdata`, `start`, `xcallAnswer`, `answer`, `end`, `duration`, `billsec`, `disposition`, `amaflags`, `accountcode`, `uniqueid`, `userfield`, `did`, `recordingfile`, `cnum`, `cnam`, `outbound_cnum`, `outbound_cnam`, `dst_cnam`, `taskID`, `taskStrategyID`, `callerNo`, `calledNo`, `extension`, `hungupTime`, `seatdtmf`, `dtmf`, `ecdtmf`, `amount`, `direction`, `siteID` from " . sofn_get_sharding_field('DB_CDR') . "cdr where $splitwhere and siteID=$siteID;";
            }
            //exit;
            $sqls[] = 'delete from ' . sofn_get_sharding_field('DB_CDR') . 'cdr where ' . $splitwhere;
            $advM = new \Think\Model\AdvModel();
            $result = $advM->patchQuery($sqls);
            if (false === $result) {
                die($advM->getDbError());
            }
        }
    }

    // 策略外呼分析
    public function seat_call_stat() {
        $search_data = $this->get_seatcall_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    function get_seatcall_search_data() {
        $search = I();
        if (!$search['beginbilldate']) {
            $search['beginbilldate'] = date('Y-m-d', strtotime('-1 week'));
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            $where['siteID'] = $this->loginData['siteID'];
        }
        if (I('beginbilldate')) {
            $where['createdTime'] = array(array('EGT', $search['beginbilldate'] . ' ' . $search['beginTime']), array('ELT', $search['beginbilldate'] . ' ' . $search['endTime']));
        } else {
            $where['createdTime'] = array(array('EGT', $search['beginbilldate'] . ' ' . $search['beginTime']), array('ELT', date('Y-m-d') . ' ' . $search['endTime']));
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_seatcall_list_json() {
        $search_data = $this->get_seatcall_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'billdate desc,siteID') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('vstat_seat_call');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,siteID,billdate,taskStrageName,callCount,connCount,connDuration,connAvgDuration,connRate,arriveSeatCount,unAnswerCount,transferRate,answerCount,seatAnswerRate,talkDuration,talkAvgDuration')->order($orderby)->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['connDurationText'] = sofn_seconds_format($row['connDurationText']);
            $row['connAvgDurationText'] = sofn_seconds_format($row['connAvgDuration']);
            $row['talkDurationText'] = sofn_seconds_format($row['talkDuration']);
            $row['talkAvgDurationText'] = sofn_seconds_format($row['talkAvgDuration']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $this->ajaxReturn(array('total' => $total, 'rows' => $out));
    }

    public function seatcall_export() {
        $search_data = $this->get_seatcall_search_data();
        $list = M('vstat_seat_call')->where($search_data['where'])->field("siteID as '租户ID',billdate as '日期',taskStrageName as '外呼策略',callCount as '呼出总量',connCount as '应答总量',connDuration as '应答总时长',connAvgDuration as '应答平均时长',connRate as '应答率',arriveSeatCount as '到队列量',unAnswerCount as '座席未接量',transferRate as '到座席率',answerCount as '座席应答量',seatAnswerRate as '座席应答率',talkDuration as '座席通话时长',talkAvgDuration as '座席通话平均时长'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 分座席通话时长统计
    public function seat_calltime_stat() {
        $search_data = $this->get_seatcalltime_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    public function get_seatcalltime_search_data() {
        $search = I();
        // 默认显示当天记录
        if (!$search['beginbilldate']) {
            $search['beginbilldate'] = date('Y-m-d');
        }
        if (!$search['endbilldate']) {
            $search['endbilldate'] = date('Y-m-d');
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        $where['createdTime'] = array(
            array('GT', $search['beginbilldate'] . ' ' . $search['beginTime']),
            array('LT', $search['endbilldate'] . ' ' . $search['endTime']),
        );
        if ($search['agentID']) {
            $where['agentID'] = $search['agentID'];
        }
        if (!$this->isSuperAdmin) {
            $where['siteID'] = $this->loginData['siteID'];
        }
        return array('where' => $where, 'search' => $search);
    }

    public function get_seatcalltime_list_json() {
        $search_data = $this->get_seatcalltime_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'billdate desc,siteID') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('vstat_extension_work');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,siteID,billdate,agentID,connCount,sec0to10,sec10to20,sec20to30,sec30to40,sec40to50,sec50to60,min1to2,min2to3,min3to5,min5to8,min8to15,min15to30,min30gt')->order($orderby)->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['allDurationText'] = sofn_seconds_format($row['allDuration']);
            $row['avgDurationText'] = sofn_seconds_format($row['avgDurationText']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $data['allDuration'] = $M->where($where)->sum('allDuration');
        $data['connCount'] = $M->where($where)->sum('connCount');
        $data['sec0to10'] = $M->where($where)->sum('sec0to10');
        $data['sec10to20'] = $M->where($where)->sum('sec10to20');
        $data['sec20to30'] = $M->where($where)->sum('sec20to30');
        $data['sec30to40'] = $M->where($where)->sum('sec30to40');
        $data['sec40to50'] = $M->where($where)->sum('sec40to50');
        $data['sec50to60'] = $M->where($where)->sum('sec50to60');
        $data['min1to2'] = $M->where($where)->sum('min1to2');
        $data['min2to3'] = $M->where($where)->sum('min2to3');
        $data['min3to5'] = $M->where($where)->sum('min3to5');
        $data['min5to8'] = $M->where($where)->sum('min5to8');
        $data['min8to15'] = $M->where($where)->sum('min8to15');
        $data['min15to30'] = $M->where($where)->sum('min15to30');
        $data['min30gt'] = $M->where($where)->sum('min30gt');
        $this->ajaxReturn(array('total' => $total, 'rows' => $out, 'footer' => $data));
    }

    public function seatcalltime_export() {
        $search_data = $this->get_seatcalltime_search_data();
        $list = M('vstat_extension_work')->where($search_data['where'])->field("siteID as '租户ID',billdate as '日期',agentID as '队列号',connCount as '队列呼通总量',sec0to10 as '0-10秒',sec10to20 as '10-20秒',sec20to30 as '20-30秒',sec30to40 as '30-40秒',sec40to50 as '40-50秒',sec50to60 as '50-60秒',min1to2 as '1-2分钟',min2to3 as '2-3分钟',min3to5 as '3-5分钟',min5to8 as '5-8分钟',min8to15 as '8-15分钟',min15to30 as '15-30分钟',min30gt as '30分钟以上'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 分队列通话时长统计
    public function queue_calltime_stat() {
        $search_data = $this->get_queuecalltime_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    public function get_queuecalltime_search_data() {
        $search = I();
        // 默认显示当天记录
        if (!$search['beginbilldate']) {
            $search['beginbilldate'] = date('Y-m-d');
        }
        if (!$search['endbilldate']) {
            $search['endbilldate'] = date('Y-m-d');
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        $where['createdTime'] = array(
            array('GT', $search['beginbilldate'] . ' ' . $search['beginTime']),
            array('LT', $search['endbilldate'] . ' ' . $search['endTime']),
        );
        if ($search['agentID']) {
            $where['agentID'] = $search['agentID'];
        }
        if (!$this->isSuperAdmin) {
            $where['siteID'] = $this->loginData['siteID'];
        }
        return array('where' => $where, 'search' => $search);
    }

    public function get_queuecalltime_list_json() {
        $search_data = $this->get_queuecalltime_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'billdate desc,siteID') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('tstat_queue_work');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,siteID,billdate,agentID,connCount,sec0to10,sec10to20,sec20to30,sec30to40,sec40to50,sec50to60,min1to2,min2to3,min3to5,min5to8,min8to15,min15to30,min30gt')->order($orderby)->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['connDurationText'] = sofn_seconds_format($row['connDurationText']);
            $row['connAvgDurationText'] = sofn_seconds_format($row['connAvgDuration']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $data['connCount'] = $M->where($where)->sum('connCount');
        $data['sec0to10'] = $M->where($where)->sum('sec0to10');
        $data['sec10to20'] = $M->where($where)->sum('sec10to20');
        $data['sec20to30'] = $M->where($where)->sum('sec20to30');
        $data['sec30to40'] = $M->where($where)->sum('sec30to40');
        $data['sec40to50'] = $M->where($where)->sum('sec40to50');
        $data['sec50to60'] = $M->where($where)->sum('sec50to60');
        $data['min1to2'] = $M->where($where)->sum('min1to2');
        $data['min2to3'] = $M->where($where)->sum('min2to3');
        $data['min3to5'] = $M->where($where)->sum('min3to5');
        $data['min5to8'] = $M->where($where)->sum('min5to8');
        $data['min8to15'] = $M->where($where)->sum('min8to15');
        $data['min15to30'] = $M->where($where)->sum('min15to30');
        $data['min30gt'] = $M->where($where)->sum('min30gt');
        $this->ajaxReturn(array('total' => $total, 'rows' => $out, 'footer' => $data));
    }

    public function queuecalltime_export() {
        $search_data = $this->get_queuecalltime_search_data();
        $list = M('tstat_queue_work')->where($search_data['where'])->field("siteID as '租户ID',billdate as '日期',agentID as '队列号',connCount as '队列呼通总量',sec0to10 as '0-10秒',sec10to20 as '10-20秒',sec20to30 as '20-30秒',sec30to40 as '30-40秒',sec40to50 as '40-50秒',sec50to60 as '50-60秒',min1to2 as '1-2分钟',min2to3 as '2-3分钟',min3to5 as '3-5分钟',min5to8 as '5-8分钟',min8to15 as '8-15分钟',min15to30 as '15-30分钟',min30gt as '30分钟以上'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 座席工作统计
    public function seat_work_stat() {
        $search_data = $this->get_seatwork_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    public function get_seatwork_search_data() {
        $search = I();
        if (!$search['date']) {
            $search['date'] = date('Y-m-d');
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        $where['createdTime'] = array(
            array('GT', $search['date'] . ' ' . $search['beginTime']),
            array('LT', $search['date'] . ' ' . $search['endTime']),
        );
        if ($search['agentID']) {
            $where['agentID'] = $search['agentID'];
        }
        if (!$this->isSuperAdmin) {
            $where['siteID'] = $this->loginData['siteID'];
        }
        if ($search['siteID']) {
            $where['siteID'] = $search['siteID'];
        }
        return array('where' => $where, 'search' => $search);
    }

    public function get_seatwork_list_json() {
        $search_data = $this->get_seatwork_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'billdate desc,siteID') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('vstat_seat_work');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,siteID,billdate,agentID,personalName,beginTime,endTime,workCount,workDuration,leaveCount,leaveDuration')->order($orderby)->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['workDurationText'] = sofn_seconds_format($row['leaveDuration']);
            $row['leaveDurationText'] = sofn_seconds_format($row['leaveDuration']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $this->ajaxReturn(array('total' => $total, 'rows' => $out));
    }

    public function seatwork_export() {
        $search_data = $this->get_seatwork_search_data();
        $list = M('vstat_seat_work')->where($search_data['where'])->field("siteID as '租户ID',billdate as '日期',agentID as '工号',personalName as '姓名',beginTime as '开始时间',endTime as '结束时间',workCount as '签入次数',workDuration as '签入时长',leaveCount as '签出次数',leaveDuration as '签出时长'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 分机外呼分析
    public function extension_call_stat() {
        $search_data = $this->get_extensioncall_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    public function get_extensioncall_search_data() {
        $search = I();
        // 默认显示一周记录
        if (!$search['beginbilldate']) {
            $search['beginbilldate'] = date('Y-m-d', strtotime('-7 day'));
        }
        if (!$search['endbilldate']) {
            $search['endbilldate'] = date('Y-m-d');
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        $where['createdTime'] = array(
            array('GT', $search['beginbilldate'] . ' ' . $search['beginTime']),
            array('LT', $search['endbilldate'] . ' ' . $search['endTime']),
        );
        if ($search['agentID']) {
            $where['agentID'] = $search['agentID'];
        }
        if (!$this->isSuperAdmin) {
            $where['siteID'] = $this->loginData['siteID'];
        }
        return array('where' => $where, 'search' => $search);
    }

    public function get_extensioncall_list_json() {
        $search_data = $this->get_extensioncall_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'billdate desc,siteID') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('vstat_extension_call');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,siteID,billdate,agentID,personalName,callCount,connCount,connDuration,connAvgDuration,connRate')->order($orderby)->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['connDurationText'] = sofn_seconds_format($row['connDurationText']);
            $row['connAvgDurationText'] = sofn_seconds_format($row['connAvgDuration']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $this->ajaxReturn(array('total' => $total, 'rows' => $out));
    }

    public function extensioncall_export() {
        $search_data = $this->get_extensioncall_search_data();
        $list = M('vstat_extension_call')->where($search_data['where'])->field("siteID as '租户ID',billdate as '日期',agentID as '工号',personalName as '姓名',callCount as '呼出总量',connCount as '应答总量',connDuration as '应答总时长',connAvgDuration as '应答平均时长',connRate as '应答率(%)'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 系统呼损统计
    public function system_call_loss_stat() {
        $search_data = $this->get_systemcallloss_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    function get_systemcallloss_search_data() {
        $search = I();
        if ($search['taskStrategyID']) {
            $where['taskStrategyID'] = $search['taskStrategyID'];
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_systemcallloss_list_json() {
        $search_data = $this->get_systemcallloss_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'id desc') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('tstat_system_call_loss');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->field('id,callerNo,calledNo,extension,talkBeginTime,talkEndTime,talklen')->order($orderby)->page($page)->select();
        $this->ajaxReturn(array('total' => $total, 'rows' => $list));
    }

    public function systemcallloss_export() {
        $search_data = $this->get_systemcallloss_search_data();
        $list = M('tstat_system_call_loss')->where($search_data['where'])->field("callerNo as '日期',calledNo as '开始时间',extension as '结束时间',talkBeginTime as '来话总数',talkEndTime as '来话未应答',talklen as '来话未到达'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 系统话务统计
    public function system_call_stat() {
        $search_data = $this->get_systemcall_search_data();
        $this->assign('search', $search_data['search']);
        $this->display();
    }

    function get_systemcall_search_data() {
        $search = I();
        if (!$search['beginbilldate']) {
            $search['beginbilldate'] = date('Y-m-d', strtotime('-1 week'));
        }
        if (!$search['beginTime']) {
            $search['beginTime'] = '00:00:00';
        }
        if (!$search['endTime']) {
            $search['endTime'] = '23:59:59';
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            $where['siteID'] = $this->loginData['siteID'];
        }
        if (I('beginbilldate')) {
            $where['createdTime'] = array(array('EGT', $search['beginbilldate'] . ' ' . $search['beginTime']), array('ELT', $search['beginbilldate'] . ' ' . $search['endTime']));
        } else {
            $where['createdTime'] = array(array('EGT', $search['beginbilldate'] . ' ' . $search['beginTime']), array('ELT', date('Y-m-d') . ' ' . $search['endTime']));
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_systemcall_list_json() {
        $search_data = $this->get_systemcall_search_data();
        $where = $search_data['where'];
        $orderby = I('sort', 'id desc') . ' ' . I('order');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $M = M('tstat_system_call');
        $total = $M->where($where)->count('id');
        $list = $M->where($where)->order($orderby)->page($page)->select();
        $this->ajaxReturn(array('total' => $total, 'rows' => $list));
    }

    public function systemcall_export() {
        $search_data = $this->get_systemcall_search_data();
        $list = M('tstat_system_call')->where($search_data['where'])->field("callerNo as '日期',callerNo as '开始时间',calledNo as '结束时间',extension as '中继号',talkBeginTime as '来话次数',talkEndTime as '来话时长(秒)',talklen as '外呼次数',talkBeginTime as '外呼时长(秒)',talkEndTime as '外呼失败次数',talklen as '通话时长'")->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 录音查询
    public function record_list() {
        $this->display();
    }

    function get_record_search_data() {
        $search = I('post.');
        $strings = false;
        if ($search['callerNo']) {
            $where['callerNo'] = array('like', '%' . $search['callerNo'] . '%');
        }
        if ($search['dst']) {
            $where['dst'] = array('like', '%' . $search['dst'] . '%');
        } else {
            $strings[] = "LENGTH(dst) > 2 and dst not like '555%'";
        }
        if ($search['date']) {
            $strings[] = "date_format(start,'%Y-%m-%d') = '" . $search['date'] . "'";
        }
        if ($search['extension']) {
            $where['extension'] = $search['extension'];
        }
        if ($search['direction']) {
            $where['direction'] = $search['direction'];
        } else {
            $where['direction'] = array('GT', 0);
        }
        if ($search['billsec']) {
            $where['billsec'][] = array('GT', $search['billsec']);
        }
        if ($search['billsecl']) {
            $where['billsec'][] = array('LT', $search['billsecl']);
        }
        if ($search['strategy']) {
            $where['taskStrategyID'] = $search['strategy'];
        }
        if ($search['task']) {
            $where['taskID'] = $search['task'];
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            $search['siteID'] = $this->loginData['siteID'];
            $where['siteID'] = $search['siteID'];
        }
        if ($strings) {
            $where['_string'] = implode(' and ', $strings);
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_record_list_json() {
        $search_data = $this->get_record_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->get_cdr_list($search_data['where'], $table);
        $this->ajaxReturn($list);
    }

    public function record_export() {
        $search_data = $this->get_record_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->scope('export')->where($search_data['where'])->cache(false)->table($table)->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 满意度查询
    public function evaluation_list() {
        $this->display();
    }

    function get_evaluation_search_data() {
        $search = I('post.');
        $strings = "ecdtmf <> ''";
        if ($search['callerNo']) {
            $where['callerNo'] = array('like', '%' . $search['callerNo'] . '%');
        }
        if ($search['dst']) {
            $where['dst'] = array('like', '%' . $search['dst'] . '%');
        } else {
            $strings[] = "LENGTH(dst) > 2 and dst not like '555%'";
        }
        if ($search['date']) {
            $strings[] = "date_format(start,'%Y-%m-%d') = '" . $search['date'] . "'";
        }
        if ($search['extension']) {
            $where['extension'] = $search['extension'];
        }
        if ($search['ecdtmf']) {
            $search['ecdtmf'] = array('like', '%' . $search['ecdtmf'] . '%');
        }
        if ($search['billsec']) {
            $where['billsec'][] = array('GT', $search['billsec']);
        }
        if ($search['billsecl']) {
            $where['billsec'][] = array('LT', $search['billsecl']);
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            $search['siteID'] = $this->loginData['siteID'];
            $where['siteID'] = $search['siteID'];
        }
        if ($strings) {
            $where['_string'] = implode(' and ', $strings);
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_evaluation_list_json() {
        $search_data = $this->get_evaluation_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->get_cdr_list($search_data['where'], $table);
        $this->ajaxReturn($list);
    }

    public function evaluation_export() {
        $search_data = $this->get_evaluation_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->scope('export')->where($search_data['where'])->cache(false)->table($table)->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

    // 留言查询
    public function message_list() {
        $this->display();
    }

    function get_message_search_data() {
        $search = I('post.');
        $strings = "recordingfile <> ''";
        if ($search['callerNo']) {
            $where['callerNo'] = array('like', '%' . $search['callerNo'] . '%');
        }
        if ($search['dst']) {
            $where['dst'] = array('like', '%' . $search['dst'] . '%');
        } else {
            $strings[] = "LENGTH(dst) > 2 and dst not like '555%'";
        }
        if ($search['date']) {
            $strings[] = "date_format(start,'%Y-%m-%d') = '" . $search['date'] . "'";
        }
        if ($search['extension']) {
            $where['extension'] = $search['extension'];
        }
        $where['direction'] = 4; // 录音方向4
        if ($search['billsec']) {
            $where['billsec'][] = array('GT', $search['billsec']);
        }
        if ($search['billsecl']) {
            $where['billsec'][] = array('LT', $search['billsecl']);
        }
        if ($this->isSuperAdmin) {
            if ($search['siteID']) {
                $where['siteID'] = $search['siteID'];
            }
        } else {
            $search['siteID'] = $this->loginData['siteID'];
            $where['siteID'] = $search['siteID'];
        }
        if ($strings) {
            $where['_string'] = implode(' and ', $strings);
        }
        return array('search' => $search, 'where' => $where);
    }

    public function get_message_list_json() {
        $search_data = $this->get_message_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->get_cdr_list($search_data['where'], $table);
        $this->ajaxReturn($list);
    }

    public function message_export() {
        $search_data = $this->get_message_search_data();
        $table = $this->get_cdr_table();
        $list = AddonD('CDR')->scope('export')->where($search_data['where'])->cache(false)->table($table)->select();
        if (!$list) {
            $this->ajaxMsg(false, '找不到符合条件的记录!');
        }
        sofn_excel_export($list);
    }

}
